{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "85f88996-cf08-4a53-b102-247e38229134",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-11-07T11:07:06.643613Z",
     "iopub.status.busy": "2024-11-07T11:07:06.643096Z",
     "iopub.status.idle": "2024-11-07T11:07:09.479768Z",
     "msg_id": "858718d1-4c7b-4252-b2de-57bd6bfdfebb",
     "shell.execute_reply": "2024-11-07T11:07:09.479010Z",
     "shell.execute_reply.started": "2024-11-07T11:07:06.643583Z"
    }
   },
   "outputs": [],
   "source": [
    "import pandas as pd \n",
    "import numpy as np \n",
    "import os \n",
    "import gc\n",
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\")\n",
    "\n",
    "from sklearn.tree import DecisionTreeClassifier\n",
    "from sklearn.ensemble import RandomForestClassifier\n",
    "from sklearn.ensemble import GradientBoostingClassifier\n",
    "from sklearn.model_selection import train_test_split\n",
    "from sklearn.metrics import roc_curve\n",
    "from sklearn.model_selection import KFold, StratifiedKFold\n",
    "from sklearn.feature_selection import RFECV\n",
    "\n",
    "import lightgbm as lgb \n",
    "from lightgbm import log_evaluation, early_stopping\n",
    "import xgboost as xgb \n",
    "import copy \n",
    "\n",
    "from sklearn.ensemble import IsolationForest\n",
    "\n",
    "import matplotlib.pyplot as plt \n",
    "from matplotlib import rcParams\n",
    "rcParams[\"font.family\"] = \"SimHei\"\n",
    "\n",
    "import seaborn as sns\n",
    "\n",
    "from itertools import combinations \n",
    "import pickle\n",
    "# from bayes_opt import BayesianOptimization\n",
    "# import optuna\n",
    "from functools import partial\n",
    "\n",
    "import networkx as nx \n",
    "from itertools import combinations\n",
    "from functools import partial\n",
    "from sklearn.metrics import roc_auc_score\n",
    "from sklearn.preprocessing import LabelEncoder\n",
    "from sklearn.preprocessing import OneHotEncoder\n",
    "from sklearn.preprocessing import MinMaxScaler,StandardScaler\n",
    "from sklearn.model_selection import train_test_split\n",
    "from sklearn.linear_model import LogisticRegression\n",
    "from sklearn.linear_model import LogisticRegressionCV\n",
    "from sklearn.metrics import confusion_matrix,accuracy_score,classification_report,roc_auc_score,log_loss\n",
    "from sklearn.tree import DecisionTreeClassifier\n",
    "import sklearn.metrics as metrics\n",
    "from sklearn.metrics import classification_report\n",
    "from sklearn.metrics import roc_curve\n",
    "from sklearn.model_selection import KFold\n",
    "from sklearn.feature_selection import RFECV\n",
    "from sklearn.model_selection import StratifiedKFold\n",
    "import sklearn.ensemble as ensemble\n",
    "from sklearn.ensemble import RandomForestClassifier\n",
    "from sklearn.model_selection import cross_val_score\n",
    "from sklearn import svm\n",
    "from sklearn.feature_selection import SelectFromModel\n",
    "\n",
    "from sklearn.ensemble import RandomForestClassifier\n",
    "from sklearn.ensemble import GradientBoostingClassifier\n",
    "from sklearn.metrics import f1_score\n",
    "\n",
    "import xgboost as xgb\n",
    "from xgboost import XGBClassifier\n",
    "\n",
    "import lightgbm as lgb\n",
    "from lightgbm import LGBMClassifier\n",
    "from lightgbm import log_evaluation, early_stopping\n",
    "\n",
    "import catboost as cbt\n",
    "from catboost import CatBoostClassifier\n",
    "\n",
    "from scipy import stats,integrate\n",
    "from scipy.stats import ks_2samp\n",
    "#from scipy.stats import kssamp\n",
    "from scipy.stats import pearsonr\n",
    "from sklearn.model_selection import RandomizedSearchCV\n",
    "from scipy.stats import uniform\n",
    "from scipy.stats import kstest\n",
    "\n",
    "import toad\n",
    "\n",
    "pd.set_option('display.max_columns', 50)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c0f923bd-1bec-46b2-8824-62214d871543",
   "metadata": {},
   "source": [
    "# 工具函数"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "aec48db7-13b0-42ab-bc57-4b98a0db7741",
   "metadata": {},
   "source": [
    "## 读取数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "e3b8f266-593c-413e-8267-9960e7d5eb96",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-11-07T11:07:11.399367Z",
     "iopub.status.busy": "2024-11-07T11:07:11.398371Z",
     "iopub.status.idle": "2024-11-07T11:07:11.409956Z",
     "msg_id": "4884a1d5-12a6-45ad-97c3-6ff61478147a",
     "shell.execute_reply": "2024-11-07T11:07:11.409168Z",
     "shell.execute_reply.started": "2024-11-07T11:07:11.399335Z"
    }
   },
   "outputs": [],
   "source": [
    "def get_data(file_name, num_rows=None):\n",
    "    train_path = \"/home/mole/work/contest/train\"\n",
    "    test_path = \"/home/mole/work/contest/A\"\n",
    "    df_train = pd.read_csv(os.path.join(train_path, file_name + \"_T.csv\"), nrows=num_rows)\n",
    "    df_test = pd.read_csv(os.path.join(test_path, file_name + \"_A.csv\"), nrows=num_rows)\n",
    "    df_train[\"is_train\"] = 1\n",
    "    df_test[\"is_train\"] = 0\n",
    "    \n",
    "    df = pd.concat(objs=[df_train, df_test],axis=0)\n",
    "    df.rename(mapper = {'DATA_DAT': '数据日期', 'CUST_NO': '客户编号', 'OPTO': '经营期限至', 'OPFROM': '经营期限自', 'ENTSTATUS': '经营状态', 'REGCAP': '注册资本', 'ESDATE': '成立日期', 'FRNAME': '法定代表人/负责人/执行事务合伙人', 'ENTTYPE_CD': '企业（机构）类型编码', 'REGPROVIN_CD': '所在省份编码', 'INDS_CD': '国民经济行业代码', 'ALTDATE': '变更日期', 'ALTITEM': '变更事项', 'PERNAME': '人员姓名', 'POSITIONCODE': '职位代码', 'PERSONAMOUNT': '人员总数量', 'WEBTYPE': '网站（网店）类型', 'WEBSITNAME': '网站（网店）名称', 'DOMAIN': '网站（网店）地址', 'ANCHEDATE': '年报日期', 'ANCHEYEAR': '年报年份', 'EXECMONEY': '执行标的', 'REGDATECLEAN': '立案时间', 'COURTNAME': '执行法院', 'CASECODE': '案号', 'PUBLISHDATECLEAN': '发布时间', 'GISTID': '执行依据文号', 'PERFORMANCE': '被执行人履行情况', 'REGDATE': '立案时间', 'FINALDATE': '终本日期', 'UNPERFMONEY': '未履行金额', 'CONDATE': '出资日期', 'SUBCONAM': '认缴出资额（万元）', 'FUNDEDRATIO': '出资比例', 'INVTYPE': '股东类型', 'CONFORM': '出资方式', 'SH_CUST_NO': '股东客户编号', 'BTD_BEGINDATE': '所属日期起', 'BTD_ENDDATE': '所属日期止', 'BTD_COLLECTCODE': '征收项目代码', 'BTD_DECLARDATE': '申报日期', 'BTD_DECLARTERM': '申报期限', 'BTD_TOTALSALE': '全部销售收入', 'BTD_TAXABLESALE': '应税销售收入', 'BTD_TAXPAYABLE': '应纳税额', 'BTD_DEDUCTAMOUNT': '减免税额', 'TR_DAT': '交易日期', 'TR_CD': '交易代码', 'CHANL_CD': '渠道代码', 'ABS_INFO': '摘要信息', 'CPT_TYP_CD': '交易对手类型代码', 'ARG_ACCT_BAL': '合约账户余额', 'ACTG_DIRET_CD': '记账方向代码', 'TRS_CSH_IND': '现转标识', 'CSH_EX_IND': '钞汇标识', 'RMB_TR_AMT': '折人民币交易金额', 'CPT_INTL_FE_CUST_IND': '对手方行内客户标识', 'INT_BNK_TR_IND': '是否跨行交易', 'SAME_NAM_IND': '同名账户标识', 'CPT_CUST_NO': '交易对手客户编号'},\n",
    "              axis=1,\n",
    "              inplace=True\n",
    "             )\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "b92cbe60-099a-4da6-b9cd-4e6cf7e7fb2d",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-11-07T11:07:12.127827Z",
     "iopub.status.busy": "2024-11-07T11:07:12.127464Z",
     "iopub.status.idle": "2024-11-07T11:07:12.133851Z",
     "msg_id": "a9af8709-e9a4-4d96-a488-977224f5725b",
     "shell.execute_reply": "2024-11-07T11:07:12.133195Z",
     "shell.execute_reply.started": "2024-11-07T11:07:12.127799Z"
    }
   },
   "outputs": [],
   "source": [
    "def agg_statistics(df, group_cols, agg_functions, name_flag, p=False):\n",
    "    \"\"\"\n",
    "    分组聚合。\n",
    "    \"\"\"\n",
    "    ga = df.groupby(by=group_cols).agg(agg_functions)\n",
    "    ga.columns = [\"{}_{}_{}\".format(e[0], e[1], name_flag) for e in ga.columns.tolist()]\n",
    "    ga.reset_index(inplace=True)\n",
    "    \n",
    "    new_cols = [col for col in ga.columns.tolist() if col not in group_cols]\n",
    "    if p is True:\n",
    "        print(\"新聚合特征：\\n\", new_cols)\n",
    "    \n",
    "    return ga, new_cols"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "d970bd24-4399-446d-9b97-fd9b5f9db18d",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-11-07T11:07:12.951490Z",
     "iopub.status.busy": "2024-11-07T11:07:12.951079Z",
     "iopub.status.idle": "2024-11-07T11:07:12.960106Z",
     "msg_id": "0aabdc54-4605-4903-883e-c884ccdbe4cb",
     "shell.execute_reply": "2024-11-07T11:07:12.959341Z",
     "shell.execute_reply.started": "2024-11-07T11:07:12.951461Z"
    }
   },
   "outputs": [],
   "source": [
    "# 趋势差分特征衍生\n",
    "def get_kurt(series_x):\n",
    "    kurt = series_x.kurt()\n",
    "    return kurt\n",
    "    \n",
    "def trend_indicator(df, group_dim_1, group_dim_2, agg_functions, name_flag, offset=-1):\n",
    "    \"\"\"\n",
    "    group_dim_1：第一维度\n",
    "    group_dim_2：第二维度\n",
    "    \"\"\"\n",
    "    df = df.sort_values(by=group_dim_2, ascending=True)\n",
    "    ga = df.groupby(by=[group_dim_1, group_dim_2]).agg(agg_functions)\n",
    "    ga.columns = [\"{}_{}_{}\".format(e[0], name_flag, e[1]) for e in ga.columns.tolist()]\n",
    "    new_features = ga.columns.tolist()\n",
    "    ga.reset_index(inplace=True)\n",
    "\n",
    "    diff_new_features = []\n",
    "    for fea in new_features:\n",
    "        t = \"一阶差分_{}_{}\".format(fea, offset)\n",
    "        diff_new_features.append(t)\n",
    "        ga[t] = ga.groupby(by=group_dim_1)[fea].diff(offset) # -1\n",
    "\n",
    "    all_new_features = new_features + diff_new_features\n",
    "    agg_functions_tmp = {}\n",
    "    for fea in all_new_features:\n",
    "        agg_functions_tmp.update({fea:['last','mean','skew',get_kurt,'std','sum','max','min']})    \n",
    "\n",
    "    ga_new, _ = agg_statistics(df=ga, group_cols=[group_dim_1], agg_functions=agg_functions_tmp, name_flag=\"差分特征_{}\".format(name_flag))\n",
    "\n",
    "    return ga_new"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cb2e163e-f4fc-45f3-a2c4-0d2aede47a94",
   "metadata": {},
   "source": [
    "# 模型训练"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "0a98d14f-2ba9-4fdb-8843-a285ea282c94",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-11-07T11:07:14.610232Z",
     "iopub.status.busy": "2024-11-07T11:07:14.609511Z",
     "iopub.status.idle": "2024-11-07T11:07:14.624957Z",
     "msg_id": "ae677d71-2c54-494e-b1d9-81409a0cfcff",
     "shell.execute_reply": "2024-11-07T11:07:14.624174Z",
     "shell.execute_reply.started": "2024-11-07T11:07:14.610200Z"
    }
   },
   "outputs": [],
   "source": [
    "def LGB_model(\n",
    "              X=None,\n",
    "              y=None,\n",
    "              params=None,\n",
    "              num_boost_round=10000,\n",
    "              categorical_feature=None,\n",
    "              cv=StratifiedKFold(n_splits=5, shuffle=True, random_state=2022)\n",
    "             ):\n",
    "\n",
    "    callbacks = [log_evaluation(period=100), early_stopping(stopping_rounds = 200)]\n",
    "    if params is None:\n",
    "        params = {\n",
    "                    \"boost\":\"gbdt\",\n",
    "                    \"objective\":\"binary\",\n",
    "                    \"metric\":\"auc\",\n",
    "                    \"max_depth\":6,\n",
    "                    \"learning_rate\":0.05,\n",
    "                    \"feature_fraction\":0.85,\n",
    "                    \"bagging_fraction\":0.85,\n",
    "                    \"bagging_freq\":5,\n",
    "                    \"max_bin\":56,\n",
    "                    \"seed\":2022,    # 随机数种子，必须设置\n",
    "                    \"verbose\":-1\n",
    "                }\n",
    "\n",
    "    columns = X.columns.tolist() \n",
    "\n",
    "    y_oof = np.zeros(X.shape[0])\n",
    "    score = 0\n",
    "    score_auc = 0\n",
    "    clfs = []\n",
    "    ks_list = []\n",
    "    for k, (trian_index, valid_index) in enumerate(cv.split(X, y)):\n",
    "\n",
    "        X_train, y_train = X.values[trian_index], y.values[trian_index]\n",
    "        X_valid, y_valid = X.values[valid_index], y.values[valid_index]\n",
    "        train_D = lgb.Dataset(data=X_train, label=y_train, feature_name=columns, categorical_feature=categorical_feature)\n",
    "        valid_D = lgb.Dataset(data=X_valid, label=y_valid, feature_name=columns, categorical_feature=categorical_feature, reference=train_D)\n",
    "\n",
    "        clf = lgb.train(params=params,\n",
    "                        train_set=train_D,\n",
    "                        valid_sets=[train_D, valid_D],\n",
    "                        valid_names=[\"Train\", \"Valid\"],\n",
    "                        num_boost_round=num_boost_round,\n",
    "                        callbacks = callbacks\n",
    "                        )\n",
    "        y_pred_valid = clf.predict(X_valid, num_iteration=clf.best_iteration)\n",
    "        y_oof[valid_index] = y_pred_valid\n",
    "        print(\"=======================================\")\n",
    "        print(\"第 {} 折，当前 KS = {:.6}\".format(k+1, get_KS(y_valid, y_pred_valid)))\n",
    "        print(\"=======================================\")\n",
    "        score = score + get_KS(y_valid, y_pred_valid)\n",
    "        score_auc = score_auc + roc_auc_score(y_valid, y_pred_valid)\n",
    "        ks_list.append(get_KS(y_valid, y_pred_valid))\n",
    "\n",
    "        # 测算最佳阈值\n",
    "\n",
    "        del X_train, X_valid, y_train, y_valid\n",
    "        gc.collect()\n",
    "\n",
    "        clfs.append(clf)\n",
    "\n",
    "    ks_list.append(score/(k+1))\n",
    "    ks_list.append(get_KS(y, y_oof))\n",
    "    print(\"平均 KS = {:.6}\".format(score/(k+1)))\n",
    "    print(\"Out of folds KS = {:.6}\".format(get_KS(y, y_oof)))\n",
    "\n",
    "    print(\"平均 AUC = {:.6}\".format(score_auc/(k+1)))\n",
    "    auc = roc_auc_score(y, y_oof)\n",
    "    print(\"Out of folds AUC = {:.6}\".format(auc))\n",
    "\n",
    "    return clfs, ks_list, get_KS(y, y_oof)\n",
    "\n",
    "## 计算KS\n",
    "def get_KS(y_true, y_pred):\n",
    "    fpr, tpr, _ = roc_curve(y_true, y_pred)\n",
    "    return max(abs((fpr-tpr)))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "003e0989-5f64-42b8-a60b-a322e6aa8035",
   "metadata": {},
   "source": [
    "## 特征重要性"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "4d93c8ff-cddb-4e50-8764-814ddb6c88db",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-11-07T11:07:16.372745Z",
     "iopub.status.busy": "2024-11-07T11:07:16.372290Z",
     "iopub.status.idle": "2024-11-07T11:07:16.379659Z",
     "msg_id": "ec33c6e9-59d7-4823-a632-15ddb32bb8d8",
     "shell.execute_reply": "2024-11-07T11:07:16.378990Z",
     "shell.execute_reply.started": "2024-11-07T11:07:16.372715Z"
    }
   },
   "outputs": [],
   "source": [
    "def get_feature_imp(clfs, imp_type='gain', feature_names=None, top_n=25):\n",
    "    \"\"\"\n",
    "    获取模型训练时的特征重要性，并绘图。\n",
    "    \"\"\"\n",
    "    feature_importances = pd.DataFrame()\n",
    "    feature_importances['feature'] = feature_names\n",
    "    for i, clf in enumerate(clfs):\n",
    "        feature_importances[str(i)] = clf.feature_importance(imp_type)\n",
    "    feature_importances['average'] = np.exp(np.log1p(feature_importances[[str(i) for i in range(len(clfs))]]).mean(axis=1))\n",
    "    \n",
    "    plt.figure(figsize=(20, 16))\n",
    "    sns.barplot(data=feature_importances.sort_values(by='average', ascending=False).head(top_n), x='average', y='feature');\n",
    "    plt.title('{} TOP feature importance over {} folds average gain'.format(top_n, 5));\n",
    "    return feature_importances.sort_values(by='average', ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5b765a82-eb46-424c-9c43-bec755069d11",
   "metadata": {},
   "source": [
    "# 标签信息表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "a3adb0a3-62d0-4672-9adc-fb30c177408b",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-11-07T11:07:17.446232Z",
     "iopub.status.busy": "2024-11-07T11:07:17.445786Z",
     "iopub.status.idle": "2024-11-07T11:07:17.517539Z",
     "msg_id": "967e318b-4498-4b9a-a959-40895e0e7178",
     "shell.execute_reply": "2024-11-07T11:07:17.516831Z",
     "shell.execute_reply.started": "2024-11-07T11:07:17.446203Z"
    }
   },
   "outputs": [],
   "source": [
    "TARGET = get_data(\"XW_ENTINFO_TARGET\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "22b94601-08ad-4f1a-a738-9a1612b9777f",
   "metadata": {},
   "source": [
    "# 企业基本信息表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "2fd52130-edef-4f98-a06d-25f509dcc000",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-11-07T11:07:25.687148Z",
     "iopub.status.busy": "2024-11-07T11:07:25.686656Z",
     "iopub.status.idle": "2024-11-07T11:07:25.977677Z",
     "msg_id": "a5921c5a-bebc-456c-ad79-84c7878d7a95",
     "shell.execute_reply": "2024-11-07T11:07:25.976923Z",
     "shell.execute_reply.started": "2024-11-07T11:07:25.687117Z"
    }
   },
   "outputs": [],
   "source": [
    "BASIC = get_data(\"XW_ENTINFO_BASIC\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "25b7f9c3-3e66-41f2-a206-9a693328dc6f",
   "metadata": {},
   "source": [
    "# 企业主要高管表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "78d31ed0-28a0-4aca-94c8-5dd124704676",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-11-07T11:07:27.844393Z",
     "iopub.status.busy": "2024-11-07T11:07:27.843915Z",
     "iopub.status.idle": "2024-11-07T11:07:28.286197Z",
     "msg_id": "208e8cc4-558e-4a7c-883c-0fd7fee6e1ef",
     "shell.execute_reply": "2024-11-07T11:07:28.285391Z",
     "shell.execute_reply.started": "2024-11-07T11:07:27.844363Z"
    }
   },
   "outputs": [],
   "source": [
    "data = get_data(\"XW_ENTINFO_PERSON\").merge(TARGET[[\"客户编号\",\"FLAG\"]], how=\"left\", on=\"客户编号\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "3377383d-7cff-406d-933b-cf319bfe7719",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-11-07T11:07:28.913640Z",
     "iopub.status.busy": "2024-11-07T11:07:28.913203Z",
     "iopub.status.idle": "2024-11-07T11:07:29.037347Z",
     "msg_id": "a22fc2c0-1d70-4d6d-89c4-a30cac2372ff",
     "shell.execute_reply": "2024-11-07T11:07:29.036550Z",
     "shell.execute_reply.started": "2024-11-07T11:07:28.913611Z"
    }
   },
   "outputs": [],
   "source": [
    "# 匹配企业成立日期\n",
    "data = data.merge(BASIC[[\"客户编号\",\"成立日期\",\"经营期限自\"]], how=\"left\", on=\"客户编号\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "fa686bbb-30f3-440d-be2c-18203b2f9b97",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-11-07T11:07:29.854096Z",
     "iopub.status.busy": "2024-11-07T11:07:29.853588Z",
     "iopub.status.idle": "2024-11-07T11:07:30.232202Z",
     "msg_id": "abf69a10-fe40-4d7a-b976-c1ac1cdae37e",
     "shell.execute_reply": "2024-11-07T11:07:30.231429Z",
     "shell.execute_reply.started": "2024-11-07T11:07:29.854066Z"
    }
   },
   "outputs": [],
   "source": [
    "data[\"数据日期\"] = data[\"数据日期\"].astype(\"str\").astype(\"datetime64[ns]\")\n",
    "data[\"成立日期\"] = data[\"成立日期\"].astype(\"str\").astype(\"datetime64[ns]\")\n",
    "data[\"经营期限自\"] = data[\"经营期限自\"].astype(\"str\").astype(\"datetime64[ns]\")\n",
    "\n",
    "# data[\"企业成立时间_天数\"] = (data[\"数据日期\"] - data[\"成立日期\"]).dt.days\n",
    "# data[\"企业成立时间_月数\"] = (data[\"数据日期\"] - data[\"成立日期\"]).dt.days // 30 + 1\n",
    "data[\"企业成立时间_年数\"] = (data[\"数据日期\"] - data[\"成立日期\"]).dt.days // 365 + 1\n",
    "\n",
    "# data[\"企业经营时间_天数\"] = (data[\"数据日期\"] - data[\"经营期限自\"]).dt.days\n",
    "# data[\"企业经营时间_月数\"] = (data[\"数据日期\"] - data[\"经营期限自\"]).dt.days // 30 + 1\n",
    "data[\"企业经营时间_年数\"] = (data[\"数据日期\"] - data[\"经营期限自\"]).dt.days // 365 + 1 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "a40985e4-454a-4811-84e8-bddb9d1ba302",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-11-07T11:07:32.159582Z",
     "iopub.status.busy": "2024-11-07T11:07:32.159107Z",
     "iopub.status.idle": "2024-11-07T11:07:32.168643Z",
     "msg_id": "8b4fe7c1-788f-43de-b360-52080d829ee4",
     "shell.execute_reply": "2024-11-07T11:07:32.167966Z",
     "shell.execute_reply.started": "2024-11-07T11:07:32.159551Z"
    }
   },
   "outputs": [],
   "source": [
    "data[\"人员数量_比上_企业成立时间_年数\"] = data[\"人员总数量\"] / data[\"企业成立时间_年数\"] \n",
    "data[\"人员数量_比上_企业经营时间_年数\"] = data[\"人员总数量\"] / data[\"企业经营时间_年数\"] "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "cdf0afcc-bea3-49b6-863a-58a3685bef15",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-11-07T11:07:34.009198Z",
     "iopub.status.busy": "2024-11-07T11:07:34.008719Z",
     "iopub.status.idle": "2024-11-07T11:07:35.867370Z",
     "msg_id": "d9df6014-5bbb-4886-a9df-61e5c328569b",
     "shell.execute_reply": "2024-11-07T11:07:35.866575Z",
     "shell.execute_reply.started": "2024-11-07T11:07:34.009168Z"
    }
   },
   "outputs": [],
   "source": [
    "def unique_counts(x):\n",
    "    return len(set(x))\n",
    "\n",
    "# 企业职位代码数量 \n",
    "tmp = data[[\"客户编号\",\"职位代码\"]].groupby(\"客户编号\").agg({\"职位代码\":[unique_counts]})\n",
    "tmp.columns = [\"职位数量\"]\n",
    "tmp.reset_index(drop=False, inplace=True)\n",
    "\n",
    "data = data.merge(tmp, how=\"left\", on=\"客户编号\")\n",
    "data[\"职位数量_比上_企业成立时间_年数\"] = data[\"职位数量\"] / data[\"企业成立时间_年数\"] \n",
    "data[\"职位数量_比上_企业经营时间_年数\"] = data[\"职位数量\"] / data[\"企业经营时间_年数\"] "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "114dfdbc-e14e-4e95-b835-fd770cc2ebd1",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-11-07T11:07:38.320789Z",
     "iopub.status.busy": "2024-11-07T11:07:38.320089Z",
     "iopub.status.idle": "2024-11-07T11:07:38.327304Z",
     "msg_id": "282208dc-5839-4691-8943-5e7281dd4a18",
     "shell.execute_reply": "2024-11-07T11:07:38.326614Z",
     "shell.execute_reply.started": "2024-11-07T11:07:38.320746Z"
    }
   },
   "outputs": [],
   "source": [
    "data[\"职位数量_比上_人员总数量\"] = data[\"职位数量\"] / data[\"人员总数量\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "869c70bb-7a7d-4415-8e9a-c6d513f5dae4",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-11-07T11:07:39.535530Z",
     "iopub.status.busy": "2024-11-07T11:07:39.535054Z",
     "iopub.status.idle": "2024-11-07T11:07:41.447067Z",
     "msg_id": "ca7278b2-7d1f-4297-9364-621fb866a906",
     "shell.execute_reply": "2024-11-07T11:07:41.446265Z",
     "shell.execute_reply.started": "2024-11-07T11:07:39.535501Z"
    }
   },
   "outputs": [],
   "source": [
    "# 企业高管数量\n",
    "tmp = data[[\"客户编号\",\"人员姓名\"]].groupby(\"客户编号\").agg({\"人员姓名\":[unique_counts]})\n",
    "tmp.columns = [\"高管数量\"]\n",
    "tmp.reset_index(drop=False, inplace=True)\n",
    "\n",
    "data = data.merge(tmp, how=\"left\", on=\"客户编号\")\n",
    "data[\"高管数量_比上_企业成立时间_年数\"] = data[\"高管数量\"] / data[\"企业成立时间_年数\"] \n",
    "data[\"高管数量_比上_企业经营时间_年数\"] = data[\"高管数量\"] / data[\"企业经营时间_年数\"] "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "e49374ac-4f1f-4b37-960a-3f7aecf2cd0a",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-11-07T11:07:41.448819Z",
     "iopub.status.busy": "2024-11-07T11:07:41.448448Z",
     "iopub.status.idle": "2024-11-07T11:07:41.453654Z",
     "msg_id": "754580ff-91be-4a65-ab90-8c2f8ced3d61",
     "shell.execute_reply": "2024-11-07T11:07:41.453007Z",
     "shell.execute_reply.started": "2024-11-07T11:07:41.448791Z"
    }
   },
   "outputs": [],
   "source": [
    "data[\"高管数量_比上_人员总数量\"] = data[\"高管数量\"] / data[\"人员总数量\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "e8c4eb3a-6fbb-46ae-b2e5-3681ff8f55ec",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-11-07T11:07:43.332912Z",
     "iopub.status.busy": "2024-11-07T11:07:43.332486Z",
     "iopub.status.idle": "2024-11-07T11:07:43.337828Z",
     "msg_id": "7cea9f12-3209-46ae-a959-6c8ac564845b",
     "shell.execute_reply": "2024-11-07T11:07:43.337094Z",
     "shell.execute_reply.started": "2024-11-07T11:07:43.332878Z"
    }
   },
   "outputs": [],
   "source": [
    "agg_functions = {\n",
    "    \"人员姓名\":[unique_counts],\n",
    "    \"职位代码\":[unique_counts],\n",
    "    \"人员总数量\":[\"max\"],\n",
    "    \"人员数量_比上_企业成立时间_年数\":[\"max\"],\n",
    "    \"人员数量_比上_企业经营时间_年数\":[\"max\"],\n",
    "    \"职位数量_比上_企业成立时间_年数\":[\"max\"],\n",
    "    \"职位数量_比上_企业经营时间_年数\":[\"max\"],\n",
    "    \"高管数量_比上_企业成立时间_年数\":[\"max\"],\n",
    "    \"高管数量_比上_企业经营时间_年数\":[\"max\"],\n",
    "    \"职位数量_比上_人员总数量\":[\"max\"],\n",
    "    \"高管数量_比上_人员总数量\":[\"max\"]\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "d0739649-0593-463f-af53-0c4a421e25ac",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-11-07T11:07:44.342347Z",
     "iopub.status.busy": "2024-11-07T11:07:44.341850Z",
     "iopub.status.idle": "2024-11-07T11:07:47.749094Z",
     "msg_id": "871966fb-8762-42b0-b306-e8bb76316b98",
     "shell.execute_reply": "2024-11-07T11:07:47.748320Z",
     "shell.execute_reply.started": "2024-11-07T11:07:44.342317Z"
    }
   },
   "outputs": [],
   "source": [
    "data, _ = agg_statistics(df=data,\n",
    "                          group_cols=[\"客户编号\"],\n",
    "                          agg_functions=agg_functions,\n",
    "                          name_flag=\"企业主要高管表\"\n",
    "                          )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "2f671e5a-cf91-4d7c-b8f3-b072edff6a37",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-11-07T11:08:41.122262Z",
     "iopub.status.busy": "2024-11-07T11:08:41.121760Z",
     "iopub.status.idle": "2024-11-07T11:08:41.161957Z",
     "msg_id": "4a54a8e3-3249-4d17-ae9c-76a7a27410a1",
     "shell.execute_reply": "2024-11-07T11:08:41.161250Z",
     "shell.execute_reply.started": "2024-11-07T11:08:41.122230Z"
    }
   },
   "outputs": [],
   "source": [
    "data.to_pickle(\"../data/数据还原_企业主要高管表_1107.pkl\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "00604b18-d009-4678-ac50-09544e3e9bcd",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.18"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
